package jpave.database;

/**********************************************************
 * 1. Called during viewSingleTCW startup to create lines for Overview
 * 2. Called during execAnno to create the annotation part of Overview
 */
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.TreeMap;
import java.util.HashMap;
import java.util.Vector;
import java.text.DecimalFormat; 

import jpave.annotator.CoreMain;
import jpave.annotator.LineParser;
import jpave.query_interface.JPaveMain;
import util.Debug.ErrorReport;
import util.methods.TimeHelpers;

public class OverviewDBWrapper {
	private final DecimalFormat df = new DecimalFormat("#,###,###");
	private final int NSPECIES = 15;
	private final int PSIM = 40; // percent similarity cutoff; if change, also change in legend
	
	public OverviewDBWrapper(Connection dbC) {
		dbConn = dbC;
	}
	/*****************************************************
	 * Called from CoreMain to create viewSingleTCW overview and store in database
	 * also called on ManagerFrame for Get State if pja_msg==null
	 */
	public String createOverview(Vector<String> lines) throws Exception
	{
        try { 
    			CoreDBWrapper.addColumnKludge(dbConn);
            	setFlags();
            	return computeOverview(lines);
        }
        catch ( Exception err ) {
	    	    ErrorReport.reportError(err,"Error: processing data for overview");
	        return null;
        }
	}
	/**
	 * called from JPaveDBWrapper on startup of viewPAVE to be displayed in Overview panel
	 */
    public boolean overview (Vector<String> lines ) throws Exception
    {   
        try {   
        		CoreDBWrapper.addColumnKludge(dbConn);
    		    setFlags();
    		    JPaveDBWrapper.setFlags(numContigs, hasBuried, hasMatePairs, hasNs, 
    	    			hasContigSets, hasPairwise, hasDBhitData, hasDBhitSeq,
    	    			hasSelfblast, hasTselfblast, partsPer, isProteinDB, hasNoAssembly, 
    	    			nContigSets, hasGO);
    	    
        	    /******************************
        	     * always create pja_msg if null
        	     */
       		stmt = dbConn.createStatement();
            rset = stmt.executeQuery("SELECT pja_msg FROM assem_msg where AID=1");
            if (rset.next() ) {
                String pga_msg = rset.getString("pja_msg");
            
                // CAS 2/6/15 added check for Project so forces database update because was 'DataSet'
                // CAS 7/7/15 add back in checking updateMSG so can force regeneration from viewSingle
                if (pga_msg != null && pga_msg.startsWith("Project:") && !JPaveMain.updateMSG) {
                    	lines.add(pga_msg);
                    	return true;
                }
                else {
                    	computeOverview(lines);
                    	return true;
                }
            }
            else { 
        	        lines.add("Error creating overview -- AID error (email tcw@agcol.arizona.edu for fix)");
            }
            stmt.close();
    	        rset.close();       
            return true;
        }
        catch ( Exception err ) {
    		    ErrorReport.reportError(err,"Error: processing data for overview");
            return false;
        }
    }
  
    /*****************************************************
     * 
     ****************************************************/
    private String computeOverview(Vector <String> lines) 
    {
        	try {   
        		stmt = dbConn.createStatement();
        		
    		    if (!computeSections(lines, true)) 	 
    		        lines.add("Error creating overview");
        	    
        		String text = "";
        		for (int i=0; i< lines.size(); i++)
        			text = text + lines.get(i) + "\n";
      
         	// If this is a SKIP_ASSEMBLY, there is no entry so it needs to be inserted
            rset = stmt.executeQuery("SELECT pja_msg FROM assem_msg where AID=1");
            if (rset.next() ) {
            	    stmt.executeUpdate("update assem_msg set pja_msg = \"" + text + "\" where AID=1"); 
            }
            else {
                stmt.executeUpdate("insert assem_msg set AID = 1, pja_msg = \"" + text + "\""); 
            }
        	    //dbConn.commit();
            rset.close();
           	stmt.close();
            			
           	return text;
        	}  
        catch ( Exception err ) {
    		    ErrorReport.reportError(err,"Error: adding overview");
            return null;
        }    
    }
    private boolean computeSections(Vector<String> lines, boolean ask) {
    		try {
		    System.err.println("Updating overview, this can take awhile on large databases, " +
		    		"\n   but only is done when database content has changed...");
		    System.err.println("   Library statistics....");
			if (!makeLibs(lines)) return false;
			if (!makeAnnoGeneralstats(lines)) return false;
			
			System.err.println("   Annotation statistics....");
			if (hasDBhitData && hasAnno) { // CAS 2/2/15 
				if (!makeAnnoSQLTable(ask)) return false; // makes species table first time this is run
				if (!makeAnnoStats(lines, ask)) return false;	
				if (!makeAnnoDB(lines)) return false;
				if (!makeAnnoSpecies(lines)) return false;
			}
			if (!makeAnnoPair(lines)) return false;	
			
			System.err.println("   Expression statistics....");
			if (!makeDEstats(lines)) return false;	
			
			System.err.println("   Sequence statistics....");
			if (!makeAssm(lines)) return false;
			if (!makeGeneLocs(lines)) return false;
			if (hasDBhitData && hasAnno) 
				if (!makeAnnoFiles(lines)) return false;
			System.err.println("Complete update overview");
			return true;
    		}
	    catch ( Exception err ) {
       		ErrorReport.reportError(err,"Error: creating overview");
            return false;
        }
    }
/**
* Libraries and Unitran Sets
**/
    private boolean makeLibs(Vector<String> lines) 
    {
	    try {	        
	        strAssmID = doStringQuery(stmt, "SELECT assemblyid from assembly");
	        if (strAssmID==null || strAssmID=="" || strAssmID.contains("Not assembled")) {
	        		strAssmID ="Instantiate not executed yet";
	        		hasTranscripts=false;
	        }
			lines.add( "Project: " + strAssmID ); // CAS 2/6/15 chg DataSet to Project
	        lines.add(" ");
	        lines.add("INPUT");
	        
	        if (!expLibs(lines)) return false;
	        if (!transLibs(lines)) return false;
	        return true;
	    }
        	catch ( Exception err ) {
        		ErrorReport.reportError(err,"Error: processing data for overview libriries");
        		return false;
        	}
    }
    
    private boolean expLibs(Vector<String> lines) {
		try {
	    		int nReadLibs = doIntQuery(stmt,"SELECT COUNT(*) FROM library " +
	        		"WHERE avglen = 0 && ctglib=0");
	    		if (nReadLibs==0) return true;
	  
	    		lines.add("   Count libraries: " + nReadLibs);	 
	      	    
	    		// CAS 5/24/15 added SEQID
	    		String [] fields = {"SEQID", "LIBID", "SIZE", "TITLE", "SPECIES", 
	    						"CULTIVAR", "STRAIN", "TISSUE", "STAGE", 
	    						"TREATMENT", "SOURCE", "YEAR"};     		
	    		int [] just = {1, 1,0,1,1, 1,1,1,1, 1,1,1};
	    
	    		int nCol=fields.length; 
	    		rows = new String[nReadLibs][nCol];
	    		int r=0;
	       
	    		rset = stmt.executeQuery("SELECT parent, libid, libsize, title, organism, cultivar, " +
    				"strain, tissue, stage, treatment, source, year  " +
    				"FROM library WHERE ctglib=0 && avglen=0");	
        	    
	        while(rset.next()) {
	            for (int i=0; i< nCol; i++) {
		        		if (just[i]==0) {
		        			int v = rset.getInt(i+1);
		        			rows[r][i] = df.format(v);
		        		}
		        		else rows[r][i] = rset.getString(i+1);
	            }
	            r++;
	        }
	        makeTable(nCol, r, fields, just, lines);
	        return true;
        	}
        	catch ( Exception err ) {
        		ErrorReport.reportError(err,"Error: processing data for overview libriries");
        		return false;
        	}       
    }
    
    private boolean transLibs(Vector<String> lines) {
        	try {
        		// CAS 5/24/15 was ctgLib=1, which means it has counts
        		// but made sequence libraries all treated the same
        		int nSets = doIntQuery ( stmt,"SELECT COUNT(*) FROM library WHERE avglen > 0");
        		if (nSets==0) return true;
            
        		lines.add("   Sequence libraries: " + nSets);
        		String[] fields2 = {"SEQID", "SIZE", "TITLE", "SPECIES", 
            			"CULTIVAR", "STRAIN", "TISSUE", "STAGE", 
            			"TREATMENT", "SOURCE", "YEAR", "AVG-len", 
            			"MED-len"};
        		int [] just2 = {1,0,1,1, 1,1,1,1, 1,1,1,0, 0};
        		int nCol=fields2.length;
        		rows = new String[nSets][nCol];
        		int r=0;
               
        		rset = stmt.executeQuery("SELECT libid, libsize," +
            		"title, organism, cultivar, strain, tissue, stage, treatment, source, year, " +
            		"avglen, medlen " +
            		"FROM library WHERE avglen>0"); // CAS 5/24/15	

	        while(rset.next()) {
		    		for (int i=0; i< nCol; i++) {
		    			if (just2[i]==0) {
		        			int v = rset.getInt(i+1);
		        			rows[r][i] = df.format(v);
		        		}
		        		else rows[r][i] = rset.getString(i+1);
	            }
	            r++;
	        }
	        makeTable(nCol, r, fields2, just2,  lines);
	        return true;
		}
		catch (Exception err) {
			ErrorReport.reportError(err,"Error: reading database for overview libraries");
			return false;
		}
    }
    /***********************************************/
    /**
     * make Annotation header stats
     **/
    private boolean makeAnnoGeneralstats(Vector<String> lines) 
    {
        lines.add( "ANNOTATION" ); 
  
        try {
        		String annoVer = doStringQuery(stmt, "SELECT annoVer from schemver");
        		if (annoVer != null)
        			lines.add("   Annotated with TCW release: " + annoVer);
            String annotationDate = doStringQuery ( stmt, "SELECT annotationdate FROM assembly");    
            if (annotationDate==null || annotationDate.equals("") || annotationDate.equals("0000-00-00")) {
                lines.add("   execAnno has not been run yet");
                lines.add(" ");
                hasAnno = false;
                return true; 
            }
            
            if (annotationDate.equals("1111-11-11"))
                lines.add("   execAnno was started but did not finish");
            else if (annotationDate.equals("1111-12-11"))
                lines.add("   Pairwise computation did not finish");
            else {
                lines.add("   Annotation date: " + TimeHelpers.convertDate(annotationDate));
            }
            hasAnno = true;
            if (isProteinDB) return true;
            
            // add ORF and GC stats
		    int longestORF = doIntQuery(stmt, "SELECT count(*) FROM contig " +
		    		"WHERE (o_coding_end - o_coding_start)>=300" );
		    double p;
		    int pLorf=0;
		    if (longestORF>0) {
		    		p = ((double) longestORF/ (double) numContigs)*100.0;
		    		pLorf = (int) p ;
		    }
		    int endORF = doIntQuery(stmt, "SELECT count(*) FROM contig " +
		     		" where o_coding_has_begin=1 && o_coding_has_end=1" );
		    int oPorf=0;
		    if (endORF>0) {
	    			p = ((double) endORF/ (double) numContigs)*100.0;
	    			oPorf = (int) p ;
		    }
		    int proteinORF = doIntQuery(stmt, "SELECT count(*) FROM contig " +
		    		"WHERE p_coding_has_begin>0" );
		    int pPorf=0;
		    if (proteinORF>0) {
		    		p = ((double) proteinORF/ (double) numContigs)*100.0;
		    		pPorf = (int) p ;
		    }
		    float avgGC = doFloatQuery(stmt, "SELECT AVG(gc_ratio) from contig");
		    avgGC *= 100.0;
		    
		    int [] just = {1, 0, 1};
	        	rows = new String[20][3];
	        int r = 0;
	 
	        rows[r][0] = "ORFs >= 300:";
	        rows[r][1] = df.format(longestORF);
	        rows[r][2] = "(" + pLorf + "%) ";
	        r++;
	        
	        rows[r][0] = "ORF has start and stop:";
	        rows[r][1] = df.format(endORF);
	        rows[r][2] = "(" + oPorf + "%) ";
	        r++;
	        
	        if (proteinORF>0) {
	        	 	rows[r][0] = "ORF protein confirmation:";
	 	        rows[r][1] = df.format(proteinORF);
	 	        rows[r][2] = "(" + pPorf + "%) ";
	 	        r++;
	        }
	        
	        rows[r][0] = "Average GC ratio:";
	        rows[r][1] = String.format("%5.1f", avgGC);
	        rows[r][2] = "";
	        r++;
	        
	        makeTable(3, r, null, just, lines); 	    
        }
        catch ( Exception err ) {
     	    	ErrorReport.reportError(err, "Error: processing data for annotation header");
    			hasAnno = false;
        		lines.add("   execAnno encountered error" );
        		return true;
     	} 	
        return true;
    }
/**
*  make Annotation stats
**/
    private boolean makeAnnoStats(Vector<String> lines, boolean ask) 
    {     				
		lines.add("   AnnoDB Statistics:");
		 
        try {
		    int nCtgHits = doIntQuery(stmt, "SELECT count(*) FROM contig WHERE bestmatchid is not null" );
		    int nUniqueHits = doIntQuery(stmt, "SELECT count(*) FROM pja_db_unique_hits ");		    
		    int nTotalHits = doIntQuery(stmt, "SELECT count(*) FROM pja_db_unitrans_hits ");
		        
		    if (nTotalHits==0 || nUniqueHits==0) {
			    lines.add("   Annotation not complete");
		        return true;
		    }
		   
		    double pp = ((double) nCtgHits/ (double) numContigs)*100.0;
            int pAnnoCtgs = (int) (pp + 0.5) ;
            
		    long sumLen = doLongQuery(stmt, "SELECT SUM(consensus_bases) from contig");
		    long sumHit = doLongQuery(stmt, "SELECT SUM(alignment_len) FROM pja_db_unitrans_hits " +
		    			" JOIN contig on contig.PID=pja_db_unitrans_hits.PID");
		    double coverage = (((double) sumHit/sumLen) * 100.0);
		    String percent = "%";
		    String cov = String.format("%5.1f%s", coverage, percent);
		    
		    int nCtgGOs=0, pCtgWithGOs=0, nHitGOs=0, pHitWithGOs=0, nSlims=0;
		    if (tableExists("go_info")) {
                nHitGOs = doIntQuery(stmt, "SELECT count(DISTINCT DUHID) FROM pja_uniprot_go" ); 
                nCtgGOs = doIntQuery(stmt, "SELECT count(DISTINCT CTGID) FROM pja_unitrans_go" );        
                nUniqueGOs = doIntQuery(stmt, "SELECT count(*) FROM go_info ");
                if (tableColumnExists("go_info", "slim"))
                		nSlims = doIntQuery(stmt, "Select count(*) from go_info where slim>0");
            
                pp = ((double) nHitGOs/ (double) nUniqueHits) * 100.0;
                pHitWithGOs = (int) (pp + 0.5);
                pp = ((double) nCtgGOs/ (double) numContigs) * 100.0;
                pCtgWithGOs = (int) (pp + 0.5);
		    }
                
	// STATISTICS
		   
	        int [] just = {1, 0, 1};
	        	rows = new String[20][3];
	        int r = 0;
	 
	        rows[r][0] = "Annotated sequences:";
	        rows[r][1] = df.format(nCtgHits);
	        rows[r][2] = " (" + pAnnoCtgs + "%)";
	        r++;
	        rows[r][0] = "Unique hits:";
	        rows[r][1] = df.format(nUniqueHits);
	        rows[r][2] = "";
	        r++;
	        rows[r][0] = "Total hits:";
	        rows[r][1] = df.format(nTotalHits);
	        rows[r][2] = "";
	        r++;
	        rows[r][0] = "Sequence coverage by hits:";
	        rows[r][1] = cov;
	        rows[r][2] = "(Bases: " + df.format(sumLen) + "; covered: " + df.format(sumHit) + ")";
	        r++;
	        if (nCtgGOs!=0) {
	            rows[r][0] = "Sequences with GOs:";
                rows[r][1] = df.format(nCtgGOs);
                rows[r][2] = " (" + pCtgWithGOs + "%)";
                r++;
    	            rows[r][0] = "Hits with GOs:";
                rows[r][1] = df.format(nHitGOs);
                rows[r][2] = " (" + pHitWithGOs + "%)";
                r++;
                rows[r][0] = "Unique GOs";
                rows[r][1] = df.format(nUniqueGOs);
                rows[r][2] = "";
                r++;
                if (nSlims > 0) {
                		rows[r][0] = "GOslims";
                    rows[r][1] = df.format(nSlims);
                    rows[r][2] = "";
                    r++;
                }
	        }
	        makeTable(3, r, null, just, lines); 
        }    
        catch ( Exception err ) {
        		ErrorReport.reportError(err,"Error: processing data for overview annotation");
        		return false;
        }
        return true;
    }
	 // ANNO DBS
    private boolean makeAnnoDB(Vector <String> lines) 
    {	  
    		try {
	        int nDBs = doIntQuery (stmt, "SELECT COUNT(*) FROM pja_databases");   
	        lines.add("   Annotation databases: " + nDBs + "   (see Legend at end)");
	        
	        String [] dfields = {"TYPE", " TAXO", " UNIQUE", " ONLY", " EVAL", " ANNO", 
	        		" TOTAL", ("SIM>=" + PSIM), ("SIM<"+ PSIM), "Subset"};
	        int [] djust = {1, 1,  0, 0, 0, 0, 0, 0, 0, 1};
	        
	        ResultSet rset = stmt.executeQuery("SELECT dbtype, taxonomy, nUniqueHits, " +
	        		" nOnlyDB, nBestHits, nOVBestHits, nTotalHits, nHighPercent, nLowPercent, subset FROM pja_databases");	
	       
	        int nCol = dfields.length;
	        rows = new String[nDBs][nCol];
	        int r=0;     
	        boolean subset=false;
	        
	        while(rset.next()) {
        	        	String s;
        	        	int i=0, j;
        	        	
        	        	s = rset.getString("dbtype");
        	        	rows[r][i++] = s;
        	        	s = rset.getString("taxonomy");
        	        	rows[r][i++] = s;
        	        	 	        
        	        	j = rset.getInt("nUniqueHits");
        	        	rows[r][i++] = df.format(j);
        	        	
        	        	j = rset.getInt("nOnlyDB");
        	        	rows[r][i++] = df.format(j);
        	        	j = rset.getInt("nBestHits");
        	        	rows[r][i++] = df.format(j);
        	        	j = rset.getInt("nOVBestHits");
        	        	rows[r][i++] = df.format(j);
        	        	j = rset.getInt("nTotalHits");
        	        	rows[r][i++] = df.format(j);
        	        	j = rset.getInt("nHighPercent");
        	        	rows[r][i++] = df.format(j);
        	        	j = rset.getInt("nLowPercent");
        	        	rows[r][i++] = df.format(j);
        	        	
        	        	j = rset.getInt("subset");
        	        	if (j==1) {
        	        		subset=true;
        	        		rows[r][i++] = "Yes";
        	        	}
        	        	else rows[r][i++] = "";
	            r++;
	        }
	        if (!subset) nCol--;
	        
	     
	        makeTable(nCol, r, dfields, djust, lines); // finish database
    		}    
        catch ( Exception err ) {
        		ErrorReport.reportError(err,"Error: processing data for overview annotation");
        		return false;
        }
    		return true;
    }
    
 // SPECIES table
    private boolean makeAnnoSpecies(Vector <String> lines) 
    {
    		ResultSet rset;
    		try {
	      
        	    int nSpecies = doIntQuery ( stmt, "SELECT count(*) FROM pja_db_species "); 
        	 	lines.add("   Top " + NSPECIES + " species from total: "+ nSpecies);
        	 	
	
        	    String [] sfields = {"SPECIES (25 char)", " EVAL", " ANNO", "TOTAL", "","SPECIES", " EVAL", " ANNO", "TOTAL"};
            int nGrp = 4;
        	    int nCol = sfields.length;
            int nRow = (int) ((float)(NSPECIES/2)+1.0);
            if (nSpecies < NSPECIES) nRow = (int) ((float)(nSpecies/2)+1.0);
            int [] justify2 = {1, 0, 0, 0, 1, 1, 0, 0, 0};
            rows = new String[nRow][nCol];
                	
	        	rset = stmt.executeQuery("SELECT species, count, nBestHits, nOVBestHits FROM pja_db_species " + 
	  			      " ORDER BY nBestHits DESC, nOVBestHits DESC, count DESC");
	    
	        	int r = 0, c = 0, inc=0, oCnt=0, oBest=0, oAnno=0, ne=0, na=0, t=0;
	        	while(rset.next()) {
	        		String s = rset.getString(1);
	        		if (s.length()>25) s = s.substring(0,25);
	        		t = rset.getInt(2);
	        		ne = rset.getInt(3);
	        		na = rset.getInt(4);

	        		if (r == nRow && inc == 0) {      			
	        		    	r = 0;
	        		    	inc = nGrp;
	        		}
	        		else if (r == nRow-1 && inc == nGrp){
	        		    	oCnt+=t;
	        		    	oBest+=ne;
	        		    	oAnno+=na;
	        		    	continue;
	        		}
	        		c=inc;
	        		if (inc == nGrp) rows[r][c++] = " ";	        		
	            	rows[r][c++] = s;	            	   		
	        		rows[r][c++] = df.format(ne);
	        		rows[r][c++] = df.format(na);
	        			
	        		if (t>1000) 
	        			 rows[r][c++] = (df.format((int) t/1000)) + "k";
	        		else rows[r][c++] = df.format(t);
	        			
	        		r++;        		
	        	}	
	        	if (inc==nGrp && r==nRow-1) {
	        		rows[r][nGrp]= " "; 
	        		rows[r][nGrp+1]= "Other";
	        		rows[r][nGrp+2] = df.format(oBest);
	        		rows[r][nGrp+3] = df.format(oAnno);
	        		
	        		if (oCnt>1000) 
	        			rows[r][nGrp+4] = (df.format((int) oCnt/1000)) + "k";
	        		else rows[r][nGrp+4] = df.format(oCnt);
        		}
	        	else if (inc==0) {nRow = r; nCol=nGrp;}
	        	
	       
	        	makeTable(nCol, nRow, sfields, justify2, lines);
	        	
    			return true;
        }
        catch ( Exception err ) {
        		ErrorReport.reportError(err,"Error: processing data for overview annotation");
        		return false;
        }
    }
    // ANNO DBS
    private boolean makeAnnoFiles(Vector<String> lines) 
    {     
    		lines.add("-------------------------------------------------------");
		lines.add("AnnoDB INFO");
		lines.add("   Files:");
		 
        try {
	        int nDBs = doIntQuery (stmt, "SELECT COUNT(*) FROM pja_databases");        

	        String [] dfields = 
	        			{"Type", "Taxo", "FILE", "DB DATE", "ADD DATE", "EXECUTE", "SUBSET"};
	        int [] djust = {1, 1, 1, 1, 1, 1, 1};

	        String strQ = "SELECT dbtype, taxonomy, path, dbdate, addDate, parameters, subset" +
	        					" FROM pja_databases";	
	        	        
	        int nCol = dfields.length;
	        rows = new String[nDBs][nCol];
	        int r=0;     
	        rset = stmt.executeQuery(strQ);
	        boolean subset=false;
	        
	        while(rset.next()) {
        	        	String s;
        	        	int i=0, j;
        	        	
        	        	s = rset.getString("dbtype");
        	        	rows[r][i++] = s;
        	        	s = rset.getString("taxonomy");
        	        	rows[r][i++] = s;
        	        	
        	        	s = rset.getString("path");
        	        	int last = s.lastIndexOf("/");
            		rows[r][i++] = s.substring(last+1);
    	        	
            		s = rset.getString("dbDate");
            		rows[r][i++] = TimeHelpers.convertDate(s);        		
            		s = rset.getString("addDate");
            		rows[r][i++] = TimeHelpers.convertDate(s);
            		
            		s = rset.getString("parameters");
            		if (s==null) s="unknown";
            		rows[r][i++] = s;
            		
            		s = rset.getString("subset");
            		if (s.equals("0")) rows[r][i++] = "no";
            		else {rows[r][i++] = "yes"; subset=true;}
        		
	            r++;
	        }
	        if (!subset) nCol--;
	        makeTable(nCol, r, dfields, djust, lines); // finish database  
	        
	        lines.add("   LEGEND:");
	        lines.add("     annoDB:");
	        lines.add("      Unique  #unique hits to this annoDB, i.e. unique annoDB identifiers");
	        lines.add("      ONLY    #TCW sequences that hit this annoDB and no others");
	        lines.add("      EVAL    #TCW sequences with the best eval from this annoDB");
	        lines.add("      ANNO    #TCW sequences with the best annotation from this annoDB ");
	        lines.add("      TOTAL   #Total sequence-hits pairs to this annoDB");
	        lines.add("      SIM>=40 #TCW sequences with >=" + PSIM + " blast %similarity to hit");
	        lines.add("      SIM< 40 #TCW sequences with <" + PSIM + " blast %similarity to hit");
	        lines.add("");
	        lines.add("     Species:");
	        lines.add("      SPECIES If the species name is >25 characters, it is truncated to 25");
	        lines.add("      EVAL    #TCW sequences with the best eval from this species");
	        lines.add("      ANNO    #TCW sequences with the best annotation from this species ");
	        lines.add("      Total   #Total hits to this species");
	        lines.add("");
	        lines.add("     ANNO details:");
	      	lines.add("      Descriptions may not contain words such as 'uncharacterized', 'hypothetical_protein', 'orf', etc");
	      	lines.add("      Precedence is given to SwissProt hits since they are manually curated");
	      
    			return true;
        }
        catch ( Exception err ) {
        		ErrorReport.reportError(err,"Error: processing data for overview annotation");
        		return false;
        }
    }
    
    private boolean makeAnnoPair (Vector<String> lines ) throws Exception
    {
		if (!hasAnno) return true;
		if (!hasPairwise) return true;
			
		lines.add("   Similar pairs:");
        int [] djust = {1, 0};
        rows = new String[20][2];
        int r = 0;
        	    
        try {
	        int nPairs = doIntQuery(stmt, "SELECT COUNT(*) FROM pja_pairwise");      
        		int self  = doIntQuery(stmt, "SELECT COUNT(*) " +                
        		        "FROM pja_pairwise WHERE in_self_blast_set = 1");
        		int tself  = doIntQuery(stmt, "SELECT COUNT(*) " +                
        		        "FROM pja_pairwise WHERE in_translated_self_blast = 1");
      
        		rows[r][0] = "Total pairs:";
        		rows[r][1] = df.format(nPairs);
        		r++;
        		rows[r][0] = "with Nucleotide selfblast:";
        		rows[r][1] = df.format(self);
        		r++;
        		rows[r][0] = "with Translated selfblast:";
        		rows[r][1] = df.format(tself);
        		r++;
            	makeTable(2, r, null, djust, lines);
        		return true;
        }
        catch (SQLException err) {
			ErrorReport.reportError(err,"Error: reading database for overview pairwise");
			return false;
		}
      }
    /***********************************************/
    /**
     * XXX make Annotation header stats
     **/
    private boolean makeDEstats(Vector<String> lines) 
    {
    		lines.add( "EXPRESSION" );
    		if (hasRPKM==false) {
    			lines.add("   None");
    			return true;
    		}
    		
    		if (!makeRPKM(lines)) return false;
    		
        if (hasCtgDE==false) return true;
   
        if (!makeDEtab("Sequence", "contig", lines)) return false;
        
        if (!hasGODE) return true; 
        if (!makeDEtab("GO", "go_info", lines)) return false;      
        return true;
    }
    private boolean makeDEtab(String title, String table, Vector<String> lines) {
        try {        
            String strQ=  "SELECT ";
            rset = stmt.executeQuery("SHOW COLUMNS FROM " + table);
            Vector <String> rowLab = new Vector <String> ();
            while(rset.next()) {
                String col = rset.getString(1);
                if(col.startsWith("P_")) {
                    if (rowLab.size() > 0) strQ += ",";
                    strQ +=  col;
                    rowLab.add(col);
                }
            }
            strQ += " FROM " + table;
            
            // TMP: I don't know if the counts makes sense for GOs, so until I know...
            if (title.equals("GO")) {
                String x = "   GO DE columns: ";
                for (int i=0; i<rowLab.size(); i++) x += rowLab.get(i).substring(2) + " ";
                lines.add(x);
                lines.add("");
                return true;
            }
            
            int total = numContigs;
            if (title.equals("GO")) total = nUniqueGOs;
            lines.add("   Differential Expression:");
            
            double [] cuts =    {     1e-5,    1e-4,    0.001,   0.01,     0.05};
            String [] dfields = {"", "<1e-5", "<1e-4", "<0.001", "<0.01", "<0.05"};
            int [] djust =      {1,       0,       0,      0,       0,        0};
            
            int nCol = dfields.length;
            rows = new String[rowLab.size()][nCol];    
            int [][] cnts = new int [rowLab.size()][cuts.length];
            rset = stmt.executeQuery(strQ);
     
            while(rset.next()) {
                for (int i=0; i<rowLab.size(); i++) {
                    double de = rset.getDouble(rowLab.get(i)); // DE values for row
                    for (int j=0; j<cuts.length; j++) {
                        if (Math.abs(de) < cuts[j]) {
                        		cnts[i][j]++;
                        }
                    }
                }
            }
            rset.close();
            
            for (int i=0; i<rowLab.size(); i++) {
                rows[i][0] = rowLab.get(i).substring(2);
                for (int j=0; j<cuts.length; j++) {
                    double pp = ((double) cnts[i][j]/ (double) total)*100.0;
                    String xp = String.format("%2.1f",pp) + "%";
                    String x = df.format(cnts[i][j]);      
                    rows[i][j+1]= String.format("%s (%5s)", x, xp);
                }
            }
                      
            makeTable(nCol, rowLab.size(), dfields, djust, lines);
            return true;    
        }
        catch (SQLException e) {
            ErrorReport.reportError(e,"Error: reading database for DE");
            return false;
        }
    }
    // XXX CAS 5July15
    private boolean makeRPKM(Vector<String> lines) {
        try {        
            String strQ=  "SELECT ";
            Vector <String> libCol = new  Vector <String> ();
            for (String l : libs) {
                String col = "LN__" + l;
                if (libCol.size() > 0) strQ += ",";
                strQ +=  col;
                libCol.add(col);
            }
            strQ += " FROM contig";
            
            String [] dfields = {"", "0-1", "2-100", "101-1000", "1001-5000", "5001-10k", ">10k"};
    	    		int[] start= {0, 2, 101,1001, 5001,10001};
    	    		int[] end=   {1,100,1000,5000,10000,100000000};
            int [] djust = {1, 0, 0, 0, 0, 0, 0};
    	    		int nCol=start.length+1;
    	    		
    	    		rows = new String[libs.size()][nCol];    
    	        int [][] cnts = new int [libs.size()][start.length];
    	        rset = stmt.executeQuery(strQ);
    	        while(rset.next()) {
                for (int i=0; i<libs.size(); i++) {
                    double ln = rset.getDouble(libCol.get(i));
                    for (int j=0; j<start.length; j++) {
                        if (ln >= start[j] && ln<=end[j]) {
                        		cnts[i][j]++;
                        		break;
                        }
                    }
                }
            }
            rset.close();
        		
    		    lines.add("   RPMK: ");
    		    for (int i=0; i<libs.size(); i++) {
                 rows[i][0] = libs.get(i);
                 for (int j=0; j<start.length; j++) {
                     double pp = ((double) cnts[i][j]/ (double) numContigs)*100.0;
                     String xp = String.format("%2.1f",pp) + "%";
                     String x = df.format(cnts[i][j]);      
                     rows[i][j+1]= String.format("%s (%5s)", x, xp);
                 }
            }
            makeTable(nCol, libs.size(), dfields, djust, lines);
    	   
            return true;    
        }
        catch (SQLException e) {
            ErrorReport.reportError(e,"Error: reading database for DE");
            return false;
        }
        catch (Exception e) {
            ErrorReport.reportError(e,"Error: reading database for DE");
            return false;
        }
    }
    /*
     * XXX make Assembly stats
     */
    private boolean makeAssm (Vector<String> lines ) throws Exception
    {
    		if (!hasTranscripts) return true;
    		
		String assemblyDate = doStringQuery ( stmt,
                "SELECT assemblydate FROM assembly");
		
		if (assemblyDate==null) return true;
		
		if (hasNoAssembly) lines.add("SEQUENCES");
		else lines.add("ASSEMBLY");
		
		lines.add("   Build date: " + TimeHelpers.convertDate(assemblyDate));
		
        int [] djust = {1, 0};
    	    rows = new String[20][2];
    	    int r = 0;
	    
	    if (hasNoAssembly) {
		    	if (isProteinDB) lines.add("   Proteins loaded from external source");
		    	else             lines.add("   Sequences loaded from external source");
				lines.add("");
		    	makeAssmLenTable(stmt, lines);
		    	return true;
	    }
	    
	    // normal assembly
	    int nClones = doIntQuery ( stmt,
		        "SELECT COUNT(*) FROM contig JOIN contclone " +
		        "ON contig.contigid=contclone.contigid");	    
	    rows[r][0] = "Reads:";
	    rows[r][1] = df.format(nClones);
	    r++;
	    
	    	if (hasBuried) {
	    		int nBuried = doIntQuery ( stmt, "SELECT COUNT(*) FROM contig " +
	    		        "JOIN contclone ON contig.contigid=contclone.contigid " +
	    		        "WHERE buried=1");
	    		rows[r][0] = "   Buried:";
	    		rows[r][1] = df.format(nBuried);
	    		r++;
	    	}
		if (hasMatePairs) {
		    int nMatePairs = doIntQuery ( stmt, "SELECT SUM(frpairs) " +
				"FROM contig");
				
		    int nLoners = doIntQuery ( stmt, "SELECT SUM(est_loners) " +
		        "FROM contig");

		    rows[r][0] = "   Mate-pairs:";
		    rows[r][1] = df.format(nMatePairs);
		    r++;
		    rows[r][0] = "   Unmated 3' & 5':";
		    rows[r][1] = df.format(nLoners);
		    r++;
		}
		
	    int nMultContigs = doIntQuery ( stmt, "SELECT count(*) FROM contig "
	            + "WHERE numclones>1" );
	    int nSingletons = doIntQuery (stmt, "SELECT count(*) FROM contig " +
	            "WHERE numclones=1" );
	    int nContigs = nSingletons + nMultContigs;
	    rows[r][0] = "Contigs:";
	    rows[r][1] = df.format(nContigs);
	    r++;
	    
	    rows[r][0] = "   Multi-read:";
	    rows[r][1] = df.format(nMultContigs);
	    r++;
	    rows[r][0] = "   Singletons:";
	    rows[r][1] = df.format(nSingletons); 
		r++;

	    if (hasMatePairs) {		
	        int mmp = doIntQuery ( stmt, "SELECT count(*) FROM contig "
	            + "WHERE numclones>1 AND !(numclones=2 AND frpairs=1)" );
	
	        int jn = doIntQuery(stmt,
	            "SELECT COUNT(*) FROM contig " + 
	            	"WHERE numclones>1 AND " +
	            "!(numclones=2 and frpairs=1) AND has_ns!=0" );
	  
	        int on = doIntQuery ( stmt,
	            "SELECT count(*) FROM contig " +
	            "WHERE numclones=2 AND frpairs=1" );
	
	        int ojn = doIntQuery (stmt,"SELECT count(*) FROM contig " +
	            "WHERE numclones=2 AND frpairs=1 AND has_ns!=0" );

		    rows[r][0] = "   Multi mate-pairs:";
		    rows[r][1] = df.format(mmp);
		    r++;
		    rows[r][0] = "      joined by n's:";
		    rows[r][1] = df.format(jn);
		    r++;
		    rows[r][0] = "   One mate-pair:";
		    rows[r][1] = df.format(on);
		    r++;
		    rows[r][0] = "      joined by n's:";
		    rows[r][1] = df.format(ojn); 
		    r++;
	    }
	    lines.add("   Statistics:");
		makeTable(2, r, null, djust, lines);
	    makeAssmSizeTable(stmt, lines);
	    makeAssmLenTable(stmt, lines);
	    return true;
    }
    private void makeAssmSizeTable(Statement stmt, Vector<String> lines) 
    	{
	    String [] dfields = {"=2", "3-5", "6-10", "11-20", "21-50", "51-100", "101-1k", ">1k"};
	    int[] start= {1,2, 5,10,20, 50, 100,1001};
	    int[] end=   {2,5,10,20,50,100,1000,100000000};
        int [] djust = {0, 0, 0, 0, 0, 0, 0, 0};
	    int n=start.length;
	    rows = new String[3][n+1];
	    
	    try {
	    		int c=0;
		    for (int i = 0; i<n; i++) {
		    	 int cnt = doIntQuery ( stmt, "SELECT count(*) FROM contig " +
		   	          "WHERE numclones > " + start[i] + " AND numclones <= " + end[i]);
		         rows[0][i] = df.format(cnt);
		         c++;
		    }
		    lines.add("   Contig expression levels: ");
		    makeTable(c, 1, dfields, djust, lines);
	    }
	    catch (Exception err) {
			ErrorReport.reportError(err,"Error: reading database for overview len table");
			return;
	    }	
    }
    private void makeAssmLenTable(Statement stmt, Vector<String> lines) {
        String [] dfields = 
		{"1-100", "101-500", "501-1000", "1001-2000", "2001-3000", "3001-4000", "4001-5000", ">5000"};
        int [] djust = {0, 0, 0, 0, 0, 0, 0, 0};
		
	    int[] start= {0,  100, 500, 1000,2000, 3000,4000,5000};
	    int[] end =  {100,500, 1000,2000,3000, 4000,5000,100000000};
	    int n = start.length;
	    rows = new String[3][n+1];
	
	    try {
		    for (int i = 0; i<n; i++) {
		       int cnt = doIntQuery ( stmt, "SELECT count(*) FROM contig " +
		         "WHERE consensus_bases > " + start[i] + " AND consensus_bases <= " + end[i]);
		        rows[0][i] = df.format(cnt);
		    }
		    if (!isProteinDB) lines.add("   Sequence lengths:");
		    else             lines.add("   Protein lengths:");
		    makeTable(n, 1, dfields, djust, lines);
	    }
	    catch (Exception err) {
			ErrorReport.reportError(err,"Error: reading database for overview len table");
			return;
	    }	
    }
    private boolean makeGeneLocs (Vector<String> lines ) throws Exception
    {
    		try {
    			ResultSet rs = stmt.executeQuery("show columns from assem_msg like 'hasLoc'");
    			if (!rs.first()) return true;
    			
    			lines.add( "LOCATIONS" );
    			String [] dfields = {"1", "2", "3-4", "5-7", "8-10", "11-20", "21-30", ">30"};
    		    int[] start= {1,2, 3,5,8, 11, 21,31};
    		    int[] end=   {2,3, 5,8,11,21, 31,100000000};
    	        int [] djust = {0, 0, 0, 0, 0, 0, 0, 0};
    	        int [] cnt = {0,0,0,0,0,0,0,0};
    		    int n=start.length;
    		    rows = new String[3][n+1];
    		    
    		    HashMap <String, Integer> seqMap = new HashMap <String, Integer> ();
    		    int cnt_pos=0, cnt_neg=0;
    		    rs = stmt.executeQuery("select seq_group, seq_strand from contig");
    		    while (rs.next()) {
    		    		String grp = rs.getString(1);
    		    		String strand = rs.getString(2);
    		    		
    		    		if (seqMap.containsKey(grp)) seqMap.put(grp, seqMap.get(grp)+1);
    		    		else seqMap.put(grp, 1);
    		    		if (strand.equals("-")) cnt_neg++; else cnt_pos++;
    		    }
    		    for (String g : seqMap.keySet()) {
    		    		int num = seqMap.get(g);
    		    		for (int i=0; i< n; i++) {
    		    			if (num < end[i]) {
    		    				cnt[i]++;
    		    				break;
    		    			}
    		    		}
    		    }
    		    
    		    for (int i=0; i<n; i++) rows[0][i] = df.format(cnt[i]);
    		    
    		    lines.add("   Transcripts on positive strand: " + cnt_pos + "  negative strand: " + cnt_neg);
    		    lines.add("   Transcripts per sequenced contig:");
    		    makeTable(n, 1, dfields, djust, lines);
    		    
    		}
    		catch (Exception err) {
    			ErrorReport.reportError(err,"Error: reading database for gene location information");
    			return false;
    		}
    		return true;
    }
    /*
     * setflags used in overview calculation and JPaveDBWrapper
     * CAS 5July15 -- there is no way to tell the difference between a single library with no expressions,
     * and an assembled library.
     * 				exploaded   ctglib   orig_libid   parent
     * translib		1			1		 
     * explib		0			0		 name		 parent
     * assmlib		0			0		 name
     * singlib		0			0		 name				# don't want L_ columns, but added before Instantiate
     */
	private boolean setFlags() {
	   
	   try {   
	       stmt = dbConn.createStatement();
		   numContigs = doIntQuery(stmt, "SELECT COUNT(*) FROM contig"); 			
		   if (numContigs == 0) {
				return false;
		   }
			
		   // If all contigs == 1, then it was probably instantiated
		   int num = doIntQuery(stmt, "SELECT COUNT(*) FROM contig WHERE numclones > 1"); 	
		   
		   if (num==0) hasNoAssembly = true;
		   else {
			   // has n's, buried, mate-pairs?
			   num = doIntQuery(stmt, "SELECT COUNT(*) FROM contig WHERE has_ns = 1 LIMIT 1"); 			
			   if (num > 0) hasNs = true;
				
			   num = doIntQuery(stmt, "SELECT COUNT(*) FROM buryclone");
			   if (num > 0) hasBuried = true;
			   
			   // has n's and buried ?
			   num = doIntQuery(stmt, "SELECT COUNT(*) FROM contig WHERE frpairs > 1 LIMIT 1"); 			
			   if (num > 0) hasMatePairs = true;
		   }
		   
		   // Unitran sets ?
		   int nSets = doIntQuery ( stmt, "SELECT COUNT(*) FROM library " +
                  "JOIN  assemlib ON (assemlib.libid = library.libid) " +
                  "WHERE library.ctglib=1");
		   if (nSets !=  0) hasContigSets = true;
		   nContigSets = nSets;
          
		   // DB hits ?
		   int nDBHits = doIntQuery ( stmt, "SELECT count(*) FROM contig "
  	                    + "WHERE bestmatchid is not null" );
  	       if (nDBHits > 0) hasDBhitData = true;
  	        
  	       // pairs ?
		   int nPairs = doIntQuery(stmt, "SELECT COUNT(*) " +                
  				"FROM pja_pairwise");
  	       if (nPairs > 0) { 
        		hasPairwise = true;	          
        		int self  = doIntQuery(stmt, "SELECT COUNT(*) " +                
				"FROM pja_pairwise WHERE in_self_blast_set = 1");
        		int tself  = doIntQuery(stmt, "SELECT COUNT(*) " +                
				"FROM pja_pairwise WHERE in_translated_self_blast = 1");
        		if (self > 0) hasSelfblast=true;
        		if (tself > 0) hasTselfblast=true;
  	       }
  	        	
  	       // partPer - obsolete
  	       int ppx = doIntQuery( stmt, "Select ppx from assembly");
  	
  	       if (ppx < 2) {
  	    	   		partsPer = String.format("%d",100);
  	       }
  	       else if (ppx <= 5) {
  	    	   		double x =  Math.pow(10, ppx)/1000;
  	    	   		partsPer = String.format("%dK", (int) x);	    	   		
  	       }
  	       else {
   	   			double x =  Math.pow(10, ppx)/1000000;
   	   			partsPer = String.format("%dM", (int) x);	
  	       }
  	       
  	       //Peptide db
  	       isProteinDB = false;
  	       ResultSet rset = stmt.executeQuery("SHOW COLUMNS FROM assem_msg");
  	       while(rset.next()) {
             String col = rset.getString(1);
             if(col.equals("peptide")) {
                 isProteinDB = true;
                 System.err.println("Reading a Protein Database");
                 break;
             }
           }
  	   	      
  	       // Expression libs; a single unassemblied library has ctglib=0 and 
  	       rset = stmt.executeQuery("Select libid  from library where ctglib=0");
  	       while (rset.next()) libs.add(rset.getString(1));
  	       if (libs.size()>0) {
	  	       	Vector <String> del = new Vector <String> ();
	  	       	for (String lib : libs) {
	  	    	   		String col = "L__" + lib;
	  	    	   		int cnt = doIntQuery(stmt, "Select count(*) from contig where " + col + ">1");
	  	    	   		if (cnt==0) del.add(lib);
	  	       	}
	  	       	// Seriously kludgy. There is no way to tell the difference between
	  	       	// A library of assembled sequences and unassembled library with no exp levels
	  	       	// that caused an error in chkLibExpLevel in setUIForGroupFromFieldsForLibrary in FieldTab
	  	       	// what a convoluted mess
  	       		for (String lib : del) {
  	       			libs.remove(lib);
  	       			//stmt.executeUpdate("update library set ctglib=1 where libid='" + lib + "'" );
	   		   }
  	       }
  	       if (libs.size()>0) hasRPKM=true;
  	       
  	       // String prefix = QRFrame.pValColPrefix;
  	       hasCtgDE=hasGODE=false;
           rset = stmt.executeQuery("SHOW COLUMNS FROM contig");
           while(rset.next()) {
               String col = rset.getString(1);
               if(col.startsWith("P_")) hasCtgDE=true;
           }
           
           if (tableExists("go_info") && tableExists("pja_gotree")) {
               hasGO = true;
           
               rset = stmt.executeQuery("SHOW COLUMNS FROM go_info");
               while(rset.next()) {
                   String col = rset.getString(1);
                   if(col.startsWith("P_")) {
                       hasGODE=true;
                       break;
                   }
               }
           }
           else hasGO = false;
         
  	       stmt.close();
  	       rset.close();
  	       return true;
	   }
	   catch (Exception err) {
			ErrorReport.reportError(err,"Error reading database");
			return false;
	   }
	}
    ////////////////////////////////////////////////////////////////////////
    // XXX 
	private void makeTable(int nCol, int nRow, String[] fields, int [] justify, Vector<String> lines)
	{
		int c, r;
		String line;
		String space = "  ";
		
		// compute column lengths
		int []collen = new int [nCol];
		for (c=0; c < nCol; c++) collen[c] = 0;
		
        for (c=0; c< nCol; c++) {
            for (r=0; r<nRow && rows[r][c] != null; r++) {
        		if (rows[r][c] == null) rows[r][c] = "";
        		if (rows[r][c].length() > collen[c]) 
        			collen[c] = rows[r][c].length();
            }
        }
        if (fields != null) {
			for (c=0; c < nCol; c++) {
				if (collen[c] > 0) {
					if (fields[c].length() > collen[c]) 
						collen[c]=fields[c].length();
				}
			}
	        // output headings
	        line = "      ";
	        for (c=0; c< nCol; c++) 
	        	if (collen[c] > 0) 
	        		line += pad(fields[c],collen[c],justify[c]) + space; // CAS 2/7/15 1->justify
	        lines.add(line);
        }
        // output rows
        for (r=0; r<nRow; r++) {
        		line = "      ";
            for (c=0; c<nCol; c++) {
                 if (collen[c] > 0) 
                	 	line += pad(rows[r][c],collen[c],justify[c]) + space;
                 rows[r][c] = ""; // so wouldn't reuse in next table
            }
            lines.add(line);
        }
        lines.add("");
	}
	
    private static String pad(String s, int width, int o)
    {
    		if (s == null) return " ";
            if (s.length() > width) {
                String t = s.substring(0, width-1);
                System.err.println("'" + s + "' truncated to '" + t + "'");
                s = t;
                s += " ";
            }
            else if (o == 0) { // left
                String t="";
                width -= s.length();
                while (width-- > 0) t += " ";
                s = t + s;
            }
            else {
                width -= s.length();
                while (width-- > 0) s += " ";
            }
            return s;
    }
    
    // XXX this is run the first annotation as the species table is not populated until now
    private boolean makeAnnoSQLTable(boolean ask) {	
    	    int MAXDB=50;
		try {
			int nSpe  = doIntQuery(stmt, "SELECT COUNT(*) FROM pja_db_species");
			if (nSpe>0) return true;
			
	 		CoreMain.PrtSpMsg(1, "Updating species tables for best hit ....");
	    	    TreeMap <String, Integer> speciesCnt = new TreeMap <String, Integer> ();
	    	    TreeMap <String, Integer> speciesEval = new TreeMap <String, Integer> ();
	    	    TreeMap <String, Integer> speciesAnno = new TreeMap <String, Integer> ();
	    	    int dbEval[] = new int [MAXDB];
	    	    int dbAnno[] = new int [MAXDB];
	    	    int dbOnly[] = new int [MAXDB];
	    	    int dbHigh[] = new int [MAXDB];
	    	    int dbLow[] = new int [MAXDB];
	    	    for (int i=0; i<MAXDB; i++) dbEval[i]=dbAnno[i]=dbOnly[i]=0;
	    	    	LineParser lp = new LineParser();
	    	    	int  cnt=0,  lastDB=-1, cntCtgDB=0, lastCtg=-1;
    	    	
	    	    	ResultSet rs = stmt.executeQuery("select " +
	    	    		" pja_db_unitrans_hits.DUHID, pja_db_unitrans_hits.CTGID, " +
	    	    		" pja_db_unique_hits.DBID, pja_db_unique_hits.species, " +
    			    " pja_db_unitrans_hits.filter_best, pja_db_unitrans_hits.filter_ovbest, " +
	    	    		" pja_db_unitrans_hits.percent_id " +
    				" from pja_db_unitrans_hits " +
    				" join pja_db_unique_hits  " +
    				" WHERE pja_db_unitrans_hits.DUHID = pja_db_unique_hits.DUHID " +
    				" order by CTGID");			
	    	   
	    		while (rs.next()) {
	    			cnt++;
	    			if (cnt % 1000 == 0) System.out.print("    processed " + cnt + "\r");
	    			int duhid = rs.getInt(1);  // index into unique hits
	    			int ctgid = rs.getInt(2); // contig index
	    			int dbid = rs.getInt(3);     // database index
	    			if (dbid > MAXDB) {
	    				CoreMain.PrtError("More than " + MAXDB + " databases (" + dbid + ")");
	    				CoreMain.die("Email tcw@agcol.arizona.edu");
	    			}
	    			if (ctgid != lastCtg) {
	    				if (cntCtgDB==1 && lastDB != -1) dbOnly[lastDB]++;
	    				lastCtg = ctgid;
	    			    lastDB = dbid; 
	    			    cntCtgDB=1;
	    			}
	    			else {
	    				if (lastDB!=dbid) cntCtgDB++;
	    			}
	    			String spec = lp.getSpecies(rs.getString(4));
	    			updateHash(spec, speciesCnt);
	    			
	    			int bestEval = rs.getInt(5);
	    			if (bestEval==1) {
	    				updateHash(spec,speciesEval);
	    				dbEval[dbid]++;
	    			}
	    			int bestAnno = rs.getInt(6);	
	    			if (bestAnno==1) {
	    				updateHash(spec,speciesAnno);
	    				dbAnno[dbid]++;
	    			}
	    			int percent = rs.getInt(7);
	    			if (percent<PSIM) dbLow[dbid]++;
	    			else dbHigh[dbid]++;
	    		}
	    		if (cntCtgDB==1 && lastDB != -1) dbOnly[lastDB]++;
	    		
	    		for ( String spec : speciesCnt.keySet() ) {
	    			int anno = 0;
	    			if (speciesAnno.containsKey(spec)) anno = speciesAnno.get(spec);
	    			int eval = 0;
	    			if (speciesEval.containsKey(spec)) eval = speciesEval.get(spec);
	    			stmt.executeUpdate("insert pja_db_species set " +
	    					"AID = 1, " + 
	    					"species = \"" + spec + "\", " +
	    					"count = " + speciesCnt.get(spec) + ", " +
	    					"nBestHits = " + eval + "," +
	    					"nOVBestHits = " + anno);
	    		}
	    		// nUnique and nTotalHits were computed in DoUniprot, and are correct
	    		stmt.executeUpdate("update pja_databases set nBestHits=0, nOVBestHits=0, nOnlyDB=0");
	    		for (int i=0; i< dbEval.length; i++) {
	    			stmt.executeUpdate("update pja_databases set " +
	    					" nBestHits = " + dbEval[i] + "," +
	    					" nOVBestHits = " + dbAnno[i] + "," +
	    					" nOnlyDB = " + dbOnly[i] + "," +
	    					" nHighPercent = " + dbHigh[i] + "," +
	    					" nLowPercent = " + dbLow[i] +
	    					" where DBID = " + i);
	    		}
	    		if (rs != null)  rs.close();
	    		return true;
		}
       	catch (Exception e) {
    			ErrorReport.reportError(e, "Error making species tables");
    			return false;
       	}
    }
  
    /**
     *  Utiliy routines
     */
	static String doStringQuery(Statement stmt,String strQuery) throws Exception
    {
		ResultSet rs = stmt.executeQuery( strQuery );
		if (rs.next ()) return rs.getString(1);
		else return "";
    }
	
    static int doIntQuery ( Statement stmt, String strQuery ) throws Exception
    {
		ResultSet rs = stmt.executeQuery( strQuery );
		rs.next ();
		return rs.getInt(1);
    }
    
    static long doLongQuery ( Statement stmt, String strQuery ) throws Exception
    {
		ResultSet rs = stmt.executeQuery( strQuery );
		rs.next ();
		return rs.getLong(1);
    }
    
    static float doFloatQuery ( Statement stmt, String strQuery ) throws Exception
    {
		ResultSet rs = stmt.executeQuery( strQuery );
		rs.next ();
		return rs.getFloat(1);
    }
    
	private void updateHash(String key, TreeMap <String, Integer> map) {
		if (map.containsKey(key)) {
			int cnt = map.get(key) + 1;		
			map.put(key, cnt);
		}
		else map.put(key, 1);
	}
	private boolean tableExists(String name) 
    {
        try {
            Statement stmt = dbConn.createStatement();        
            ResultSet rs = stmt.executeQuery("show tables like '" + name + "'");
            return rs.first();
        }
        catch(Exception e)
        {
            ErrorReport.die(e);
        }
        return false;
    }
	private boolean tableColumnExists(String table, String column) throws Exception
	{
		boolean ret=false;
		Statement stmt = dbConn.createStatement();		
		ResultSet rs = stmt.executeQuery("show columns from " + table + " where field='" + column + "'");
		if (rs.first()) ret = true;
		rs.close();
		return ret;
	}
	
	private String strAssmID = "";
	private boolean hasAnno = true, hasTranscripts=true;; 
	// set flags
	private int numContigs = 0, nContigSets=0, nUniqueGOs=0;
    private boolean hasNs=false, hasMatePairs=false, hasBuried=false;
    private boolean hasContigSets = false, hasNoAssembly = false;
    private boolean hasDBhitData = false, hasDBhitSeq=false, hasPairwise=false;
    private boolean hasSelfblast = false, hasTselfblast = false;
    private boolean isProteinDB = false, hasGO = true, hasRPKM=false, hasCtgDE=false, hasGODE=false;
    private String  partsPer = "";
    private Vector <String> libs = new Vector <String> ();

	private Connection dbConn = null;
    Statement stmt = null;
    ResultSet rset = null;
    
	private String [][] rows = null;
}
